use East2West
go
-- Store Procedure List 
create proc sp_ListAllPackageType
as
	select * from PackageType
go

create proc sp_ListAllCarModel
as
	select * from CarModel
go

create proc sp_ListAllLocations
as
	select * from Locations
go

create proc sp_ListPlaceByLocationID 
	@locationID int
as
	select * from Places where LocationID = @locationID
go

create proc sp_ListHotelJoinPlace
as
	select h.HotelID,h.PlaceID,h.HotelName,h.HotelRate,h.HotelPrice,p.PlaceName 
	from Hotels h inner join Places p 
	on h.PlaceID = p.PlaceID
go

create proc sp_ListPackageTourByPackageType 
	@typeID int
as
	select * from PackageTour where PackageTypeID = @typeID
go

create proc sp_ListCarDetailByModel
	@model int
as
	select * from CarDetails where ModelID = @model
go

create proc sp_ListAllFeedback
as
select * from Feedback
go

-- Store Procedure Login
create proc sp_LoginCustomer
	@username nvarchar(50),
	@password nvarchar(50)
as
	select * from Customer where UserName = @username and Password = @password
go

create proc sp_LoginAdmin 
	@username nvarchar(50),
	@password nvarchar(50)
as
	select * from Admin where UserName = @username and Password = @password
go

-- Store Procedure Register
create proc sp_Register 
	@username nvarchar(50),
	@password nvarchar(50),
	@customername nvarchar(200),
	@email nvarchar(200),
	@phonenumber nvarchar(200),
	@address nvarchar(max)
as
	insert into Customer values (@username,@password,@customername,@email,@phonenumber,@address)
go

-- Store Procedure Feedback
create proc sp_Feedback 
	@cusID int,
	@title nvarchar(200),
	@content nvarchar(max)
as
	insert into FeedBack(CustomerID,Title,TextContent,FeedbackDay) values (@cusID,@title,@content,getdate())
go

-- Store Procedure Management PackageType
create proc sp_NewPackageType 
	@name nvarchar(200),
	@description nvarchar(max)
as
	insert into PackageType values (@name,@description)
go

create proc sp_UpdatePackageType 
	@packageTypeID int,
	@name nvarchar(200),
	@description nvarchar(max)
as
	update PackageType set PackageName = @name , Description = @description
	where PackageTypeID = @packageTypeID 
go

-- Store Procedure Management Location
create proc sp_NewLocation
	@name nvarchar(200),
	@description nvarchar(max)
as
	insert into Locations values (@name,@description)

go

create proc sp_UpdateLocation
	@locationID int,
	@name nvarchar(200),
	@description nvarchar(max)
as
	update Locations set LocationName = @name , Description = @description
	where LocationID = @locationID 
go

--Store Procedure Management Places
create proc sp_NewPlace 
	@locationID int,
	@name nvarchar(200),
	@description nvarchar(max)
as
	insert into Places values (@locationID, @name, @description)
go 

create proc sp_UpdatePlace
	@placeID int,
	@locationID int,
	@name nvarchar(200),
	@description nvarchar(max)
as
	update Places set LocationID = @locationID, PlaceName = @name, Description = @description 
	where PlaceID = @placeID 
go

--Store Procedure Management Hotels
create proc sp_NewHotel
	@placeID int,
	@name nvarchar(200),
	@rate int,
	@price money,
	@images nvarchar(200)	
as
	insert into Hotels values (@placeID,@name,@rate,@price,@images)
go 

create proc sp_UpdateHotel
	@hotelID int,
	@placeID int,
	@name nvarchar(200),
	@rate int,
	@price money
as
	update Hotels set PlaceID = @placeID , HotelName = @name , HotelRate = @rate , HotelPrice = @price
	where HotelID = @hotelID
go	

create proc sp_GetRowInHotel
as
exec sp_spaceused Hotels
go

create proc sp_ListHotelByPage
	@indexpage int
as
select * from (
	select *,row_number() over (order by HotelID) as RN from Hotels
)a
where RN >= (@indexpage - 1)*3 + 1 and RN <= (@indexpage * 3)
go

-- Store Procedure management Flight --
create proc sp_ListAllFlight
as
select * from Flight

go

create proc sp_NewFlight 
	@flightname nvarchar(200),
	@departtime datetime,
	@arrivaltime datetime,
	@departlocal int,
	@arrivallocal int
as
	insert into Flight values (@flightname,@departtime,@arrivaltime,@departlocal,@arrivallocal)
go

create proc sp_UpdateFlight
	@flightid int,
	@flightname nvarchar(200),
	@departtime datetime,
	@arrivaltime datetime,
	@departlocal int,
	@arrivallocal int
as
	update Flight set FlightName = @flightname , DepartTime = @departtime , ArrivalTime = @arrivaltime,
	DepartLocation = @departlocal , ArrivalLocation = @arrivallocal
	where FlightID = @flightid
go

-- Store Procdedure for Search action
create proc sp_SearchFlightByLocation 
	@local nvarchar(200)
as
	select * from Flight f inner join Locations l
	on f.DepartLocation = l.LocationID or f.ArrivalLocation = l.LocationID
	where l.LocationName like '%'+@local+'%' or l.LocationName like '%'+@local+'%'
go

create proc sp_SearchFlightByName 
	@name nvarchar(200)
as
	select * from Flight where FlightName like '%'+@name+'%'
go

create proc sp_SearchFlightByDate
	@dateFrom nvarchar(50),
	@dateTo nvarchar(50)
as
	select * from Flight flight where DepartTime between @dateFrom and @dateTo
go

create proc sp_advanceSearchHotel 
	@keyword nvarchar(200),
	@placename nvarchar(200),
	@pricefrom money,
	@priceto money
as
	select * from Hotels h inner join Places p on h.PlaceID = p.PlaceID
	where (@keyword = '' or h.HotelName like '%'+@keyword+'%')
	and (@placename = '' or p.PlaceName like '%'+@placename+'%')
	and (@pricefrom = 0 or h.HotelPrice >= @pricefrom)
	and (@priceto = 0 or h.HotelPrice <= @priceto)
go

create proc sp_advanceSearchCar
	@carname nvarchar(200),
	@pricefrom money,
	@priceto money
as
	select * from CarDetails where (@carname = '' or CarName like '%'+@carname+'%')
	and (@pricefrom = 0 or Price>=@pricefrom)
	and (@priceto = 0 or Price <= @priceto)
go

create proc sp_advanceSearchFlight 
	@departlocal nvarchar(50),
	@arrivallocal nvarchar(50),
	@departtime nvarchar(100),
	@arrivaltime nvarchar(100)
as
	select * from Flight f inner join Locations dl on f.DepartLocation = dl.LocationID
	inner join Locations al on f.ArrivalLocation = al.LocationID 
	where (@departlocal = '' or (dl.LocationName like '%'+@departlocal+'%'))
	and (@arrivallocal = '' or (al.LocationName like '%'+@arrivallocal+'%'))
	and (@departtime = '' or (f.DepartTime >= @departtime))
	and (@arrivaltime = '' or (f.DepartTime <= @arrivaltime))
go

create proc sp_advanceSearchTour
	@locationname nvarchar(50),
	@pricefrom money,
	@priceto money
as
	select * from PackageTour p inner join Locations l
	on p.LocationID = l.LocationID
	where (@locationname = '' or l.LocationName = @locationname)
	and (@pricefrom = 0 or Price>=@pricefrom)
	and (@priceto = 0 or Price <= @priceto)
go

create proc sp_SearchHotelByPlace
	@placename nvarchar(200)
as
	select * from Hotels h inner join Places p
	on h.PlaceID = p.PlaceID
	where p.PlaceName like '%'+@placename+'%'
go

create proc sp_SearchHotelByName 
	@hotelname nvarchar(200)
as
	select * from Hotels where HotelName like '%'+@hotelname+'%'
go

create proc sp_SearchHotelByPrice 
	@pricefrom money,
	@priceto money
as
	select * from Hotels where HotelPrice between @pricefrom and @priceto
go

create proc sp_SearchTourBookingByUserName
	@username varchar(50)
as
	select * from TourBooking t inner join Customer c
	on t.CustomerID = c.CustomerID
	where c.customerName = @username
go

create proc sp_SearchCarByPrice 
	@pricefrom money,
	@priceto money
as
	select * from CarDetails where Price between @pricefrom and @priceto
go

create proc sp_SearchTourByPrice 
	@pricefrom money,
	@priceto money
as
	select * from PackageTour where Price between @pricefrom and @priceto
go

---store proc manage package tour-----
--create a new tour

---list all packagetour
go
create proc sp_ListHotel
as
Select * from Hotels
--list all place
go
create proc sp_ListPlace
as
select * from Places

go
--list all Location
create proc sp_ListLocation 
as
select * from Locations

go
--list all package tour with status available
create proc sp_ListPackageTour
as

select * from PackageTour where Available =1
go
--list all package tour manager admin
create proc sp_ListPackageTour1
as

select * from PackageTour where Available =1
go
--list hotel following placeid
create proc sp_ListHotelByPlaceID 
	@placeID int
as
	select * from Hotels where PlaceID = @placeID


go
--Get Details Package tour by TourID
create proc sp_GetDetailsPackageTourByID
	@TourID int
as
	SELECT ptour.PackageTourName,ptour.Price,ptour.Travel,ptour.Description,ptour.Images,convert(nvarchar,ptour.FromDate,105) FromDate,convert(nvarchar,ptour.ToDate,105) ToDate,ptype.PackageName,h.HotelName,h.HotelRate,l.LocationName,p.PlaceName
	FROM PackageTour ptour join PackageType ptype
	ON ptour.PackageTypeID=ptype.PackageTypeID join Hotels h ON ptour.HotelID=h.HotelID 
	join Locations l ON ptour.LocationID=l.LocationID 
	join Places p ON ptour.PlaceID=p.PlaceID WHERE ptour.PackageTourID=@TourID

-- thuc thi lenh xoa proc newtour va updatecarmodel rooi chay lai proc new tour phia tren cho t nhe
go
create proc sp_UpdateCarModel
	@modelID int,
	@name nvarchar(200),
	@description nvarchar(max)
as
	update CarModel set ModelName = @name, Description = @description where ModelID=@modelID
go
create proc sp_NewTour
    @packagetype int,
	@packageTourName nvarchar(100),
	@hotelid int,
    @locationid int,
    @placeid int,
    @price money,
    @travel nvarchar(100),
    @des nvarchar(max),
    @image nvarchar(200),
    @fromdate nvarchar(200),
    @todate nvarchar(200),
    @available bit
as
   insert into PackageTour values (@packageTourName,@packagetype,@hotelid,@locationid,@placeid,@price,@travel,@des,@image,@fromdate,@todate,@available)

--manager Car model
--Store Procedure Management CarModel
go
create proc sp_NewCarModel
	@name nvarchar(200),
	@description nvarchar(max)
as
	insert into CarModel values (@name,@description)
go
--update tour

create proc sp_UpdateTour
    @packagetype int,
	@tourid int,
	@packageTourName nvarchar(100),
	@hotelid int,
    @locationid int,
    @placeid int,
    @price money,
    @travel nvarchar(100),
    @des nvarchar(max),
    @image nvarchar(200),
    @fromdate nvarchar(100),
    @todate nvarchar(100),
    @available bit
as update PackageTour set PackageTourName=@packageTourName,PackageTypeID=@packagetype,HotelID=@hotelid,
LocationID=@locationid,PlaceID=@placeid,Price=@price,Travel=@travel,Description=@des,Images=@image,FromDate=@fromdate,ToDate=@todate,
Available=@available where PackageTourID=@tourid

--manager car detail
--creat car detail
go
create proc sp_CreateCarDetail
	@modelid int,
	@carname nvarchar(200),
	@capacity int,
	@des nvarchar(max),
	@price money,
	@images nvarchar(200)
as
	insert into CarDetails values(@modelid,@carname,@capacity,@des,@price,@images)
go
--update car detail

create proc sp_UpdateCardetail
	@carid int,
	@modelid int,
	@carname nvarchar(200),
	@capacity int,
	@des nvarchar(max),
	@price money,
	@images nvarchar(200)
as 
	update CarDetails set ModelID=@modelid,CarName=@carname,Capacity=@capacity,Description=@des,Price=@price,Images=@images where CarID=@carid
--list all CarDetails
go
create proc sp_ListAllCar
as

select * from CarDetails
--get property of object pktour
go
create proc sp_objectPktour
@tourid int
as
select * from PackageTour where PackageTourID=@tourid

go
--Get Car By CarID
create proc sp_GetCarByID
	@CarID int
as

	SELECT * FROM CarDetails WHERE CarID=@CarID
go	

create proc sp_getRowInCarDetail
as
exec sp_spaceused CarDetails
go

create proc sp_ListCarDetailByPage
	@indexpage int
as
select * from (
	select *,row_number() over (order by CarID) as RN from CarDetails
)a
where RN >= (@indexpage - 1)*3 + 1 and RN <= (@indexpage * 3)
go

create proc sp_getRowInPackageTour
as
exec sp_spaceused PackageTour
go
create proc sp_ListPKTByPage
@indexpage int
as
select * from (
	select *,row_number() over (order by PackageTourID) as RN from PackageTour
)a
where RN >= (@indexpage - 1)*3 + 1 and RN <= (@indexpage * 3)

GO
-- Insert into TourBooking
CREATE PROC sp_CreateTourBooking
	@CustomerID int,
	@PackageTourID int,
	@Quantity int,
	@TotalPrice money
AS
	INSERT INTO TourBooking(CustomerID,PackageTourID,Quantity,TotalPrice) VALUES (@CustomerID,@PackageTourID,@Quantity,@TotalPrice)
--proc search pktour
go
create proc sp_SearchByTourName
@tourname nvarchar(100)
as
select top 5 * from PackageTour where PackageTourName like '%'+@tourname+'%'
go

create proc sp_SearchByCarName
@carname nvarchar(200)
as
select top 5 * from CarDetails where CarName like '%'+@carname+'%'

go
--Create Car Booking
CREATE PROC sp_CreateCarBooking
	@CarID int,
	@CustomerID int,
	@Driver bit,
	@PickupDate datetime,
	@DropoffDate datetime,
	@Quantity int,
	@TotalPrice money
AS
	INSERT INTO CarBooking(CarID,CustomerID,Driver,PickupDate,DropoffDate,Quantity,TotalPrice)
	VALUES (@CarID,@CustomerID,@Driver,@PickupDate,@DropoffDate,@Quantity,@TotalPrice)
go

--Store Procedure Analysis

create proc sp_AnalysisCar
as
select cb.CarID,sum(cb.Quantity) as Quantity,sum(cb.TotalPrice) as Total, cd.CarName , 
sum(cb.TotalPrice)/(select sum(TotalPrice) from CarBooking )*100 as Percents
from CarBooking cb inner join CarDetails cd
on cb.CarID = cd.CarID 
group by cb.CarID,cd.CarName order by sum(cb.Quantity)  desc

go

create proc sp_AnalysisTour
as
select tb.PackageTourID,sum(tb.Quantity) as Quantity, sum(tb.TotalPrice) as Total , 
sum(tb.TotalPrice)/(select sum(TotalPrice) from TourBooking )*100 as Percents ,pt.PackageTourName
from TourBooking tb inner join PackageTour pt
on tb.PackageTourID = pt.PackageTourID
group by tb.PackageTourID,pt.PackageTourName order by sum(Quantity) desc
go

create proc sp_AnalysisPackagetype
as
select pty.PackageName, convert(money,sum(tb.Quantity))/convert(money,(select sum(Quantity) from TourBooking))*100 as Percents
from TourBooking tb inner join PackageTour pt
on pt.PackageTourID = tb.PackageTourID
inner join PackageType pty
on pt.PackageTypeID = pty.PackageTypeID
group by pt.PackageTypeID,pty.PackageName

	
GO
--Get TourBooking by tourBookingID
CREATE PROC sp_GetTourBookingByID
	@TourBookingID int
AS
	SELECT * FROM TourBooking WHERE TourBookingID=@TourBookingID
GO
--Get CarBooking by carBookingID
CREATE PROC sp_GetCarBookingByID
	@CarBookingID int
AS
	SELECT * FROM CarBooking WHERE CarBookingID=@CarBookingID

go
/* search tour according state */

create proc sp_SearchTourByState
	@state int
as
	select top 5 * from PackageTour where Available=@state

/*-------------------- manager booking tour--------------------*/
go
/* show all orders tour*/

create proc sp_ListOrderTour
as 
	select * from TourBooking t inner join PackageTour p on t.PackageTourID=p.PackageTourID where p.FromDate> getDate()
go
/*update state of orders tour*/

create proc sp_UpdateStatusTourBooking
   @id int,
	@status bit
as
	update TourBooking set Available =@status  where TourBookingID=@id

/*get username from id*/
go

create proc sp_getUserName 
	@iduser int
as
	select UserName from Customer where CustomerID=@iduser
/*get package Name From id Package tour*/
go
create proc sp_getPackageName
	@idpackagetour int
as
	select PackageTourName from PackageTour

go
/*get list booking car rental*/

create proc sp_ListBookingCar
	
as
	select * from CarBooking where Available=1 and PickupDate > getDate()

go
/*create proc get car name*/

create proc sp_getCarName
	@intcar int
as
	select CarName from CarDetails where CarID=@intcar

go
/**create proc cancel Car Booking*/

create proc sp_updateStatusCarBooking
	@idBooking int
as
	update CarBooking set Available=0 where CarBookingID=@idBooking
/* create proc search TourBooking by UserName*/
go
create proc sp_searchBookingByUserName
	@username varchar(30)
as
	select * from TourBooking t inner join Customer c on t.CustomerID=c.CustomerID where UserName like '%'+@username+'%'